package com.shw.loco.database;

import java.util.ArrayList;
import java.util.List;

import com.shw.loco.vo.RFIDBoardInfo;
import com.shw.loco.vo.RFIDDBInfo;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 
 * @author HAHA
 * 
 */
public class DatabaseHelper extends SQLiteOpenHelper {

	// private final static String DATABASE_NAME =
	// Environment.getExternalStorageDirectory().getPath()+"/SHW/shw_loco.db";
	private final static String DATABASE_NAME = "shw_loco.db";
	private final static int DATABASE_VERSION = 1;

	private final String RFID_TABLE = "RFID";
	private final String RFID_Coor_TABLE = "COORDINATE";
	private final String AREA_TABLE = "AREA";

	private final String CREATE_RFID_TABLE = "CREATE TABLE IF NOT EXISTS "
			+ RFID_TABLE
			+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, rfid TEXT not null not null, rfid_effect TEXT, rfid_track TEXT not null, "
			+ " rfid_coordinate TEXT not null, rfid_switchnum TEXT, rfid_crossnum TEXT,rfid_crossdirection TEXT,rfid_relpostion TEXT,rfid_targetpostion TEXT,lon TEXT, lat TEXT,latlon TEXT,areano TEXT not null)";
	private final String CREATE_RFID_COOR_TABLE = "CREATE TABLE IF NOT EXISTS "
			+ RFID_Coor_TABLE
			+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, equ_name TEXT not null, equ_type INTEGER, equ_track TEXT, "
			+ " equ_coordinate1 TEXT, equ_coordinate2 TEXT,equ_coordinate3 TEXT)";
	private final String CREATE_AREA_TABLE = "CREATE TABLE IF NOT EXISTS "
			+ AREA_TABLE
			+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, lon TEXT not null, lat TEXT not null, mile_coor TEXT not null, "
			+ " areano TEXT not null, track TEXT not null,switch TEXT,latlon TEXT not null)";

	public DatabaseHelper(Context mContext) {
		super(mContext, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_RFID_TABLE);
		db.execSQL(CREATE_RFID_COOR_TABLE);
		db.execSQL(CREATE_AREA_TABLE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

		 autoDropstoreDatabase(db);
		 onCreate(db);

	}

	private void autoDropstoreDatabase(SQLiteDatabase db) {

		db.execSQL("DROP TABLE IF EXISTS " + RFID_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + RFID_Coor_TABLE);
		db.execSQL("DROP TABLE IF EXISTS " + AREA_TABLE);
	}
	
	public void alterTable()throws Exception{
		SQLiteDatabase db = this.getWritableDatabase();
		try {
		autoDropstoreDatabase(db);
		 onCreate(db);
		} finally {
			db.close();
		}
	}

	public void deleteRFID(DataType storeDataType, String rfid) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(getTableName(storeDataType), " rfid = ? ",
					new String[] { rfid });
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}

	}

	public void delete(DataType storeDataType, String whereClause,
			String[] whereArgs) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(getTableName(storeDataType), whereClause, whereArgs);
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}

	}

	public void delete(DataType storeDataType) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			db.delete(getTableName(storeDataType), null, null);
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}

	}

	public void update(DataType storeDataType, String rfid, ContentValues values)
			throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			db.update(getTableName(storeDataType), values, "rfid = ? ",
					new String[] { rfid });
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			// db.close();
		}
	}

	public void update(DataType storeDataType, ContentValues values,
			String where, String[] selectionArgs) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			db.update(getTableName(storeDataType), values, where, selectionArgs);
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}
	}

	public long insert(DataType storeDataType, ContentValues values)
			throws Exception {
		long rowId = -1;
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			rowId = db.insert(getTableName(storeDataType), null, values);
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}
		return rowId;
	}

	public void insertAllData(String sql, List<Object[]> list) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			for (int i = 0; i < list.size(); i++) {
				db.execSQL(sql, list.get(i));
			}
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}
	}
	
	public void insertRFIDData(String sql, List<Object[]> list) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
//		onUpgrade(db, 1, 1);
		try { 
			db.beginTransaction();
			for (int i = 0; i < list.size(); i++) {
				Cursor cursor = query(DataType.RFID, "rfid='"
						+ list.get(i)[0] + "'");
				if (cursor.moveToFirst()) {
					db.delete(getTableName(DataType.RFID), " rfid = ? ",new String[] { (String) list.get(i)[0] });
				} 
				db.execSQL(sql, list.get(i));
			}
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally {
			db.close();
		}
	}
	
	public void insertTestData()throws Exception{
		SQLiteDatabase db = this.getWritableDatabase();
		List<Object[]> list1=new ArrayList<Object[]>();
		List<Object[]> list2=new ArrayList<Object[]>();
		try {
			db.beginTransaction();
			Object[] areaObj={"108.89963833333333","34.237208333333335","2","1","1","","143.13684666666666"};
			list1.add(areaObj);
			for (int i = 0; i < list1.size(); i++) {
				Cursor cursor = query(DataType.AREA, "mile_coor='"
						+ list1.get(i)[3] + "'");
				if (cursor.moveToFirst()) {
					db.delete(getTableName(DataType.AREA), " mile_coor = ? ",new String[] { (String) list1.get(i)[2] });
 				} 
				db.execSQL("insert into AREA( lon, lat, mile_coor, areano, track,switch,latlon) values(?,?,?,?,?,?,?)", list1.get(i));
			}
			Object[] rfidObj={"2","","01","10","","","","","","108.89930593666668","34.23736837166666","143.13667430833334","2"};
			list2.add(rfidObj);
			for (int i = 0; i < list2.size(); i++) {
				Cursor cursor = query(DataType.RFID, "rfid='"
						+ list2.get(i)[3] + "'");
				if (cursor.moveToFirst()) {
					db.delete(getTableName(DataType.RFID), " rfid = ? ",new String[] { (String) list2.get(i)[3] });
				} 
				db.execSQL("insert into RFID ( rfid, rfid_effect, rfid_track, rfid_coordinate, rfid_switchnum , rfid_crossnum,rfid_crossdirection,rfid_relpostion,rfid_targetpostion,lon,lat,latlon,areano) values(?,?,?,?,?,?,?,?,?,?,?,?,?)", list2.get(i));
			}
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally { 
			db.close(); 
		}
	}
	
	public void insertAreaData(String sql, List<Object[]> list) throws Exception {
		SQLiteDatabase db = this.getWritableDatabase();
		try {
			db.beginTransaction();
			for (int i = 0; i < list.size(); i++) {
				Cursor cursor = query(DataType.AREA, "mile_coor='"
						+ list.get(i)[2] + "'");
				if (cursor.moveToFirst()) {
					db.delete(getTableName(DataType.AREA), " mile_coor = ? ",new String[] { (String) list.get(i)[2] });
				} 
				db.execSQL(sql, list.get(i));
			}
			db.setTransactionSuccessful();
			db.endTransaction();
		} finally { 
			db.close(); 
		}
	}

	public Cursor query(DataType storeDataType, String selection,
			String[] selectionArgs, String groupBy, String having,
			String orderBy) {
		SQLiteDatabase db = this.getReadableDatabase();
		return db.query(getTableName(storeDataType), null, selection,
				selectionArgs, groupBy, having, orderBy);
	}

	public Cursor query(DataType storeDataType, String whereCase) {
		SQLiteDatabase db = this.getReadableDatabase();
		return db.rawQuery("select * from " + getTableName(storeDataType)
				+ " where " + whereCase, null);

	}
	
	public Cursor query(String sql) {
		SQLiteDatabase db = this.getReadableDatabase();
		return db.rawQuery(sql, null);

	}

	public Cursor query(DataType bookstoreDataType, String[] columns,
			String selection, String[] selectionArgs, String orderBy) {
		SQLiteDatabase db = this.getReadableDatabase();
		return db.query(getTableName(bookstoreDataType), columns, selection,
				selectionArgs, null, null, orderBy);
	}

	private String getTableName(DataType bookstoreDataType) {
		String retval = null;
		switch (bookstoreDataType) {
		case RFID:
			retval = RFID_TABLE;
			break;
		case COORDINATE:
			retval = RFID_Coor_TABLE;
			break;
		case AREA:
			retval = AREA_TABLE;
			break;
		default:
			retval = null;
			break;
		}
		return retval;
	}

}
